package com.cg.dao;

import com.cg.Student;
import com.cg.utl.JDBCUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDao {
    /**
     * 获取学生列表
     *
     * @return 学生列表
     */
    public List<Student> selectList() {
        try {
            List<Student> list = new ArrayList<>();
            //从数据库获取实例
            JDBCUtil util = JDBCUtil.getInstance();
            String sql = "select * from student_2";
            ResultSet rs = util.query(sql);
            while (rs.next()) {
                String stuNo = rs.getString("stu_no");
                String stuName = rs.getString("stu_name");
                int age = rs.getInt("age");
                String major = rs.getString("major");
                Student student = new Student();
                student.setStuNo(stuNo);
                student.setStuName(stuName);
                student.setAge(age);
                student.setMajor(major);
                list.add(student);
            }
            util.close();
            return list;
        } catch (SQLException ex) {
            System.out.println();
            return null;
        }
    }
        /**
         * 根据学生编号获取总数量
         * @param stuNo 学生编号
         * @return 总数量
         */
        public int getTotalByStuNo(String stuNo) {
            try{
                JDBCUtil util=JDBCUtil.getInstance();
                //判断学生编号是否重复
                String selSQL="select count(1) as total from student_2 where stu_no=?";
                List<Object> selParams=new ArrayList<>();
                selParams.add(stuNo);
                ResultSet rs= util.query(selSQL,selParams);
                rs.next();
                int total= rs.getInt("total");
                util.close();
                return total;
            }catch (SQLException ex){
                System.out.println(ex.getMessage());
                return -1;
            }
        }

    /**
     *新增学生到学生表
     * @param student 学生
     * @return 是否成功
     */
    public boolean insert(Student student){
        JDBCUtil util = JDBCUtil.getInstance();
        String insertSQL = "insert into student_2(stu_no,stu_name,age,major)"
                + "values(?,?,?,?,?)";
        List<Object> params = new ArrayList<>();
        params.add(student.getStuNo());
        params.add(student.getStuName());
        params.add(student.getAge());
        params.add(student.getMajor());
        params.add(new Date());
        boolean result = util.update(insertSQL,params);
        util.close();
        return result;
    }

    /**
     * 根据学号删除学生
     * @param stuNo 学号
     * @return 是否成功
     */
    public boolean delete(String stuNo){
        JDBCUtil util =JDBCUtil.getInstance();
        String deleteSQL="delete from student_2 where stu_no=?";
        List<Object> params =new ArrayList<>();
        params.add(stuNo);
        boolean result =util.update(deleteSQL,params);
        util.close();
        return result;
    }
}
